Skip Headers

Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# OPEN-FOR Statement

The `OPEN-FOR` statement executes the query associated with a cursor variable. It allocates database resources to process the query and identifies the result set -- the rows that meet the query conditions. The cursor variable is positioned before the first row in the result set. For more information, see "Using Cursor Variables (REF CURSORs)".

With the optional `USING` clause, the statement associates a cursor variable with a query, executes the query, identifies the result set, positions the cursor before the first row in the result set, then zeroes the rows-processed count kept by `%ROWCOUNT`. For more information, see "Building a Dynamic Query with Dynamic SQL". Because this statement can use bind variables to make the SQL processing more efficient, use the `OPEN-FOR-USING` statement when building a query where you know the `WHERE` clauses in advance. Use the `OPEN-FOR` statement when you need the flexibility to process a dynamic query with an unknown number of `WHERE` clauses.

Syntax

open for statement ::=

Description of the illustration open_for_statement.gif

using clause ::=

Description of the illustration open_for_using_statement.gif

Keyword and Parameter Description

bind_argument

An expression whose value is passed to the dynamic `SELECT` statement.

cursor_variable_name

A cursor variable or parameter (without a return type) previously declared within the current scope.

host_cursor_variable_name

A cursor variable previously declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.

select_statement

A query associated with `cursor_variable`, which returns a set of values. The query can reference bind variables and PL/SQL variables, parameters, and functions. The syntax of `select_statement` is similar to the syntax for `select_into_statement` defined in "SELECT INTO Statement", except that the cursor `select_statement` cannot have an `INTO` clause. The length of the dynamic string cannot exceed 32767K.

USING clause

This optional clause specifies a list of bind arguments. At run time, bind arguments in the `USING` clause replace corresponding placeholders in the dynamic `SELECT` statement.

Usage Notes

You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To open the host cursor variable, you can pass it as a bind variable to an anonymous PL/SQL block. You can reduce network traffic by grouping `OPEN-FOR` statements. For example, the following PL/SQL block opens five cursor variables in a single round-trip:

`/* anonymous PL/SQL block in host environment */`
`BEGIN`
`OPEN :emp_cv FOR SELECT * FROM employees;`
`OPEN :dept_cv FOR SELECT * FROM departments;`
`OPEN :grade_cv FOR SELECT * FROM salgrade;`
`OPEN :pay_cv FOR SELECT * FROM payroll;`
`OPEN :ins_cv FOR SELECT * FROM insurance;`
`END;`

Other `OPEN-FOR` statements can open the same cursor variable for different queries. You do not need to close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.

Unlike cursors, cursor variables do not take parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. Although a PL/SQL stored procedure or function can open a cursor variable and pass it back to a calling subprogram, the calling and called subprograms must be in the same instance. You cannot pass or return cursor variables to procedures and functions called through database links. When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the `IN` `OUT` mode. That way, the subprogram can pass an open cursor back to the caller.

You use three statements to process a dynamic multi-row query: `OPEN-FOR-USING`, `FETCH`, and `CLOSE`. First, you `OPEN` a cursor variable `FOR` a multi-row query. Then, you `FETCH` rows from the result set. When all the rows are processed, you `CLOSE` the cursor variable.

The dynamic string can contain any multi-row `SELECT` statement (without the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.

Every placeholder in the dynamic string must be associated with a bind argument in the `USING` clause. Numeric, character, and string literals are allowed in the `USING` clause, but Boolean literals (`TRUE`, `FALSE`, `NULL`) are not. To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".

Any bind arguments in the query are evaluated only when the cursor variable is opened. To fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.

Dynamic SQL supports all the SQL datatypes. For example, bind arguments can be collections, `LOB`s, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. For instance, bind arguments cannot be `BOOLEAN`s or index-by tables.

Examples

For examples, see the following:

Example 6-27, "Passing a REF CURSOR as a Parameter"
Example 6-29, "Stored Procedure to Open a Ref Cursor"
Example 6-30, "Stored Procedure to Open Ref Cursors with Different Queries"
Example 6-31, "Cursor Variable with Different Return Types"
Example 6-32, "Fetching from a Cursor Variable into a Record"
Example 6-33, "Fetching from a Cursor Variable into Collections"
Example 7-9, "Dynamic SQL Fetching into a Record"

Related Topics

"CLOSE Statement"
"Cursor Variables"
"EXECUTE IMMEDIATE Statement"
"FETCH Statement"
"LOOP Statements"